use RecommendationSys;

go

create view V_AdminLogin
as
select * from AdminLogin

go

create view V_Cap
as
select * from Cap

go

create view V_ConstractState
as
select * from ContractState

go

create view V_CustomerContract
as
select * from CustomerContract

go

create view V_Customer
as
select * from Customer

go

create view V_ExtraData
as
select * from ExtraData

go

create view V_ExtraMessage
as
select * from ExtraMessage

go

create View V_HandsetAttributeWeight
as
select * from HandsetAttributeWeight

go

create view V_HandsetBasicInformation
as
select * from HandsetBasicInformation

go

create view V_HandsetBrand
as
select * from HandsetBrand

go

create view V_HandsetContract
as
select * from HandsetContract

go

create view V_HandsetSimilarity
as
select * from HandsetSimilarity

go

create view V_HandsetImage
as
select * from HandsetImage

go

create view V_Occupation
as
select * from Occupation

go

create view V_Plans
as
select * from Plans

go

create view V_Rating
as
select * from Rating

go

create view V_Syslog
as
select * from SysLog

go

create view V_UserLogin
as
select * from UserLogin

go

create view V_CustomerUsageHistroy
as
select * from CustomerUsageHistroy

go

create view V_PopularHandset
as
select * from PopularHandset

go
--select * from V_HandsetInformation
create view V_HandsetInformation
as
	select h.handsetID,b.brandName,h.handsetModole,h.handsetSize,h.handsetWeight,h.handsetCamera,h.handsetMemory,
	h.handsetSreenSize,h.handsetStandByTime,h.handsetSystem,h.handsetBrowset,h.handsetConnectivity,
	i.imageName,i.imagePath,i.imageID
	from V_HandsetBrand b left join V_HandsetBasicInformation h
	on b.brandID=h.brandID inner join V_HandsetImage i on h.handsetID=i.handsetID 

go

--select * from V_capdetail
create view V_CapDetail
as
select c.capID,p.planID,p.monthlyFee,b.brandName,h.handsetModole,h.handsetID,p.planName
from V_HandsetBrand b right join V_HandsetBasicInformation h
on b.brandID=h.brandID inner join V_Cap c on h.handsetID=c.handsetID 
left join Plans p on p.planID=c.planID
go


create view V_ContractDetail
as
select cc.contractID,cc.duration,v.monthlyFee,cc.startDate,cc.endDate,
v.brandName,v.handsetModole,hc.cellphoneNumber,hc.exDataID,hc.exMessageID,
c.customerName,c.customerEmail,c.customerGender,c.customerID
from V_Customer c, V_CustomerContract cc left join V_HandsetContract hc
on cc.contractID=hc.contractID left join V_CapDetail v
on hc.capID=v.capID
where c.customerID=cc.customerID


go


create view V_HotSaleHandset
as
SELECT TOP 10 h.brandName,h.handsetModole,h.handsetSize
      ,h.handsetWeight,h.handsetCamera,h.handsetMemory
      ,h.handsetSreenSize,h.handsetStandByTime
  FROM V_PopularHandset p, V_HandsetInformation h
  where h.handsetID= p.handsetID
  
 go
 
